
/**
	AUTHOR		: VEERU
	DATE		: JULY 22nd 2005
	DESCRIPTION	: THIS PROCEDURE IS USED FOR PROJECT REPROT.
	USAGE           : EXEC SP_LOAD_PROJECTS_REPORT '0','0','0','0' 
*/


ALTER  PROC SP_LOAD_PROJECTS_REPORT


@ODM_INPUT INT,
@PRODUCT_INPUT INT,
@SALESPERSON_INPUT INT,
@DEFAULT_PROMT VARCHAR(50) 


AS 
BEGIN
	DECLARE @PARTID      INT	
	DECLARE @ODM_ID      INT
	DECLARE @PROJECT     VARCHAR(100) 
	DECLARE @PARTS       VARCHAR(3500) 
	DECLARE @PART        VARCHAR(50)
	DECLARE @CUSTOMER    VARCHAR(50)
	DECLARE @ODM	     VARCHAR(50)
	DECLARE @PROJSTATUS  VARCHAR(50)
	DECLARE @SALESPERSON VARCHAR(50)


	DECLARE @USERLIST1 VARCHAR(100) 
	DECLARE	@USERLIST2 VARCHAR(100) 
	DECLARE @USERLIST3 VARCHAR(100) 
	DECLARE	@USERDATE1 VARCHAR(100) 
	DECLARE	@USERTXT1  VARCHAR(100) 
	DECLARE @USERTXT2  VARCHAR(100) 
	DECLARE	@USERTXT3  VARCHAR(100) 
	DECLARE @USERTXT4  VARCHAR(100) 
	DECLARE @USERTXT5  VARCHAR(100)
	DECLARE	@USERLIST4 VARCHAR(100) 
	DECLARE @USERLIST5 VARCHAR(100) 

	DECLARE @USERLIST1VAL VARCHAR(100) 
	DECLARE	@USERLIST2VAL VARCHAR(100) 
	DECLARE @USERLIST3VAL VARCHAR(100) 
	DECLARE	@USERDATE1VAL VARCHAR(100) 
	DECLARE	@USERTXT1VAL  VARCHAR(100) 
	DECLARE @USERTXT2VAL  VARCHAR(100) 
	DECLARE	@USERTXT3VAL  VARCHAR(100) 
	DECLARE @USERTXT4VAL  VARCHAR(100) 
	DECLARE @USERTXT5VAL  VARCHAR(100)
	DECLARE	@USERLIST4VAL VARCHAR(100) 
	DECLARE @USERLIST5VAL VARCHAR(100) 
	DECLARE @PROJID INT

	DECLARE @RESULTS TABLE ( SALESPERSON VARCHAR(50), PROJECT VARCHAR(100), CUSTOMER VARCHAR(50), ODM VARCHAR(50), PROJSTATUS VARCHAR(100), PARTS VARCHAR(3500), USERLIST1 VARCHAR(100), USERLIST2 VARCHAR(100), USERLIST3 VARCHAR(100), USERDATE1 VARCHAR(100), USERTXT1 VARCHAR(100), USERTXT2 VARCHAR(100), USERTXT3 VARCHAR(100), USERTXT4 VARCHAR(100), USERTXT5 VARCHAR(100), USERLIST4 VARCHAR(100), USERLIST5 VARCHAR(100), USERLIST1VAL VARCHAR(100), USERLIST2VAL VARCHAR(100), USERLIST3VAL VARCHAR(100), USERDATE1VAL VARCHAR(100), USERTXT1VAL VARCHAR(100), USERTXT2VAL VARCHAR(100), USERTXT3VAL VARCHAR(100), USERTXT4VAL VARCHAR(100), USERTXT5VAL VARCHAR(100), USERLIST4VAL VARCHAR(100), USERLIST5VAL VARCHAR(100))

	SELECT @USERTXT1 = USERTXT1, @USERTXT2 = USERTXT2, @USERTXT3 = USERTXT3, @USERTXT4 = USERTXT4, @USERTXT5 = USERTXT5, @USERDATE1 = USERDATE1, @USERLIST1 = USERLIST1, @USERLIST2 = USERLIST2, @USERLIST3 = USERLIST3, @USERLIST4 = USERLIST4, @USERLIST5 = USERLIST5  FROM PROJSETUP WHERE PROJID = ( SELECT MAX(PROJID) FROM PROJSETUP )

	DECLARE @SQLSTRING NVARCHAR(3000)
	DECLARE @PARAMDEFINITION NVARCHAR(500)		

	SET @SQLSTRING = N'DECLARE PROJECT_CURSOR CURSOR FOR SELECT PROJID FROM PROJSETUP WHERE PROJDISABLED = 1 ' 
	SET @PARAMDEFINITION = N'@PRM_SALESPERSON_INPUT INT, @PRM_ODM_INPUT INT, @PRM_PRODUCT_INPUT INT'

	
	IF @PRODUCT_INPUT <> '0' AND  @PRODUCT_INPUT <> '-1'
	BEGIN		
		SET @SQLSTRING = @SQLSTRING + ' AND PROJID IN ( SELECT PROJID FROM PROJPRODREL WHERE PRODID = @PRM_PRODUCT_INPUT  ) '
	END
	IF @SALESPERSON_INPUT <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND SPERSONID = @PRM_SALESPERSON_INPUT '
	END	
	IF @ODM_INPUT <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND ( ODMID = @PRM_ODM_INPUT OR CUSTID = @PRM_ODM_INPUT )' 
	END	

	
	EXECUTE SP_EXECUTESQL @SQLSTRING, @PARAMDEFINITION, @PRM_SALESPERSON_INPUT = @SALESPERSON_INPUT, @PRM_PRODUCT_INPUT = @PRODUCT_INPUT, @PRM_ODM_INPUT = @ODM_INPUT
	
	DECLARE @PART_NUMBER_TYPE INT
	SELECT @PART_NUMBER_TYPE = PART_NUMBER_TYPE FROM SYSTEMPRFS			

	OPEN PROJECT_CURSOR
	FETCH NEXT FROM PROJECT_CURSOR INTO @PROJID
	WHILE @@FETCH_STATUS = 0 
	BEGIN
		
		SELECT @USERLIST1VAL = USERLIST1VAL, @USERLIST2VAL = USERLIST2VAL, @USERLIST3VAL = USERLIST3VAL, @USERLIST4VAL = USERLIST4VAL, @USERLIST5VAL = USERLIST5VAL, @PROJSTATUS = PROJSTATUS, @ODM_ID = ODMID FROM PROJSETUP WHERE PROJID = @PROJID
		SELECT @USERDATE1VAL = ENTRYVALUE FROM FCUENTRYVAL WHERE TABLENAME = 'PROJSETUP' AND ENTRYNAME = @USERDATE1  AND PROJID = @PROJID
		SELECT @USERTXT1VAL  = ENTRYVALUE FROM FCUENTRYVAL WHERE TABLENAME = 'PROJSETUP' AND ENTRYNAME = @USERTXT1   AND PROJID = @PROJID
		SELECT @USERTXT2VAL  = ENTRYVALUE FROM FCUENTRYVAL WHERE TABLENAME = 'PROJSETUP' AND ENTRYNAME = @USERTXT2   AND PROJID = @PROJID
		SELECT @USERTXT3VAL  = ENTRYVALUE FROM FCUENTRYVAL WHERE TABLENAME = 'PROJSETUP' AND ENTRYNAME = @USERTXT3   AND PROJID = @PROJID
		SELECT @USERTXT4VAL  = ENTRYVALUE FROM FCUENTRYVAL WHERE TABLENAME = 'PROJSETUP' AND ENTRYNAME = @USERTXT4   AND PROJID = @PROJID
		SELECT @USERTXT5VAL  = ENTRYVALUE FROM FCUENTRYVAL WHERE TABLENAME = 'PROJSETUP' AND ENTRYNAME = @USERTXT5   AND PROJID = @PROJID       	

		IF @PART_NUMBER_TYPE <> 1	/* FOR MARKETING PART NUMBERS */
		BEGIN
			SELECT @SALESPERSON = SALESPERSON, @PROJECT = PROJECT, @CUSTOMER = CUSTOMER, @ODM = ODM, @PARTS = MKTG_PARTS FROM FN_LOAD_PROJECTS() WHERE PROJECTID = @PROJID
		END
		ELSE	/* FOR PART NUMBERS */
		BEGIN
			SELECT @SALESPERSON = SALESPERSON, @PROJECT = PROJECT, @CUSTOMER = CUSTOMER, @ODM = ODM, @PARTS = PARTS FROM FN_LOAD_PROJECTS() WHERE PROJECTID = @PROJID
		END

		IF ( @ODM_ID IS NULL )
		BEGIN
			SET @ODM = @CUSTOMER
		END
		INSERT INTO @RESULTS( SALESPERSON , PROJECT , CUSTOMER , ODM , PROJSTATUS, PARTS, USERLIST1 , USERLIST2 , USERLIST3 , USERDATE1 , USERTXT1 , USERTXT2 , USERTXT3 , USERTXT4 , USERTXT5 , USERLIST4 , USERLIST5 , USERLIST1VAL , USERLIST2VAL , USERLIST3VAL , USERDATE1VAL, USERTXT1VAL , USERTXT2VAL , USERTXT3VAL , USERTXT4VAL , USERTXT5VAL , USERLIST4VAL , USERLIST5VAL) VALUES				       
				     ( @SALESPERSON, @PROJECT, @CUSTOMER, @ODM, @PROJSTATUS, @PARTS, @USERLIST1, @USERLIST2, @USERLIST3, @USERDATE1, @USERTXT1, @USERTXT2, @USERTXT3, @USERTXT4, @USERTXT5, @USERLIST4 , @USERLIST5 , @USERLIST1VAL , @USERLIST2VAL , @USERLIST3VAL , @USERDATE1VAL , @USERTXT1VAL , @USERTXT2VAL , @USERTXT3VAL , @USERTXT4VAL , @USERTXT5VAL , @USERLIST4VAL , @USERLIST5VAL)
		
		SET @PROJECT       = NULL
		SET @PARTS         = NULL
		SET @PART          = NULL
		SET @CUSTOMER      = NULL
		SET @ODM	   = NULL
		SET @PROJSTATUS    = NULL
		SET @SALESPERSON   = NULL					
		SET @USERLIST1VAL  = NULL
		SET @USERLIST2VAL  = NULL
		SET @USERLIST3VAL  = NULL
		SET @USERDATE1VAL  = NULL
		SET @USERTXT1VAL   = NULL
		SET @USERTXT2VAL   = NULL
		SET @USERTXT3VAL   = NULL
		SET @USERTXT4VAL   = NULL
		SET @USERTXT5VAL   = NULL
		SET @USERLIST4VAL  = NULL
		SET @USERLIST5VAL  = NULL	
		FETCH NEXT FROM PROJECT_CURSOR INTO @PROJID
	END
	CLOSE PROJECT_CURSOR
	DEALLOCATE PROJECT_CURSOR
	
	SELECT * FROM @RESULTS
END

